I have an Excel 2013 workbook with an external SQL datasource. There are a number of calculated columns to the right of the data table as well.
After making changes to the data within SQL, I routinely refresh the data which usually takes @ 20-30 seconds.
Today I made a change to the SQL query for the datasource to change a couple of the data fields from straight out of the SQL table to conditional values using "select, case, when" clauses (both include isnull() functions in the clauses).
Now when refreshing Excel seems to move at the same speed until it gets to the 'updating cells' step where it's now taking as much as 5 or more minutes to complete, which it finally does with no errors.
So what exactly is happening at the 'updating cells' step? I thought that step handled the calculated columns of the table only and that the data was already refreshed to the table by this point. I would love to have an outline of the steps that Excel performs when one refreshes external data.
As a footnote, I do have one cell calculation as shown below which utilizes the two columns of data that I changed in the SQL view. Don't know if the 'updating cells' could possibly only be referring to re-calcing of that cell or not.
Formula: =((COUNTIFS(U:U,"Yes",N:N,FALSE,P:P,FALSE))/2)/(COUNTA(T:T)-1)
Follow-up:
1) By clearing all of the data from the table before refreshing I can see that the new data is not present in the table prior to reaching the 'updating cells' step.
2) I removed the conditional clauses in the SQL query, but it's still taking 10-15 minutes to complete the refresh.
Follow-up 2: Thought it might be a lack of memory on the SQL server (running at 83%) so I restarted the SQL server. No improvement: it's still taking 15-16 minutes to complete the Excel refr
Other recent topics
